﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BusinessLogic;

namespace ACM
{
    public partial class ImportCarAdvance : BasePage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            NotAllowRole("dealer", "Search.aspx");          
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string FileName = lblFileName.Text;
            string Extension = Path.GetExtension(FileName);
            string FolderPath = Server.MapPath(ConfigurationManager.AppSettings["FolderPath"]);
            DataTable dt;

            try
            {
                dt = ProcessExcel(FolderPath + FileName);
                ImportCarAdvanceCommand cmd = new ImportCarAdvanceCommand(dt);
                cmd.Execute();

                lblMessage.ForeColor = System.Drawing.Color.Green;
                lblMessage.Text = cmd.Result.ToString() + " records inserted.";
            }
            catch (Exception ex)
            {
                lblMessage.ForeColor = System.Drawing.Color.Red;
                lblMessage.Text = ex.Message;
            }
            finally
            {
                Panel1.Visible = true;
                Panel2.Visible = false;
            }
        }

        protected void btnCancel_Click(object sender, EventArgs e)
        {
            Response.Redirect("SearchCarAdvance.aspx");
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {

                string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string FileNames = Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName);
                string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName).ToLower();

                if (Extension != ".xls")
                //if (Extension != ".xlsx")
                {
                    Status.ShowError("Upload File ผิดประเภท");
                    return;
                }

                string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
                int TimeZoneDiff = Convert.ToInt32(ConfigurationManager.AppSettings["TimeZoneDiff"].ToString());

                string FilePath = Server.MapPath(FolderPath + FileNames + DateTime.Now.AddHours(TimeZoneDiff).ToString("_yyyymmdd_hhmmss") + Extension);

                FileUpload1.SaveAs(FilePath);

                lblFileName.Text = Path.GetFileName(FilePath);
                Panel2.Visible = true;
                Panel1.Visible = false;
            }
        }


        private DataTable ProcessExcel(string filepath)
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
            //string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";

            OleDbConnection exconn = new OleDbConnection(strConnection);
            exconn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("SELECT vin,car_series,model,suffix,sale_date FROM [Sheet1$] ", exconn);
            da.Fill(dt);
            exconn.Close();

            return dt;
        }
    }
}